﻿IF NOT (EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'RainyPWS'))
	CREATE DATABASE RainyPWS collate Chinese_PRC_CI_AS
GO

use [RainyPWS]
Go

--==========================================================================================
--Delete existing tables: TAdmin, TRecord, TRecordType, TComment
--==========================================================================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TAdmin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [dbo].[TAdmin]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TRecord]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [dbo].[TRecord]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TRecordType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [dbo].[TRecordType]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TComment]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [dbo].[TComment]
GO

--==========================================================================================
--Create tables: TAdmin, TRecordType, TRecord, TComment
--==========================================================================================
Create table [dbo].[TAdmin](
	[ID]		int Identity(1,1) Not null,
	[UserCode]	nvarchar(20)	Not null,
	[Password]	nvarchar(50)	Not null,
	[UserName]	nvarchar(20)	null,
	[Level]		int				Not null,
	[LastLogin]	datetime		null,
	[Remark]	nvarchar(500)	null,
	constraint [PK_TAdmin] Primary key nonclustered ([ID] asc),
	CONSTRAINT [UC_TAdmin_UserCode] UNIQUE NONCLUSTERED ([UserCode] ASC)
) on [Primary]
Go

Create table [dbo].[TRecordType](
	[ID]		int Identity(1,1)	Not null,
	[Name]		nvarchar(20)			Not null,
	[Remark]	nvarchar(100)			null
	constraint [PK_TRecordType] Primary key nonclustered ([ID] asc),
) on [Primary]
Go

Create table [dbo].[TRecord](
	[ID]		int Identity(1,1)	Not null,
	[Title]		nvarchar(30)		Not null,
	[TypeID]	int					Not null,
	[Status]	int					Not null, -- 0:草稿；1:发布；2:删除
	[Author]	nvarchar(20)		Not null,
	[AddedDate]	datetime			Not null,
	[Summary]	nvarchar(400)		null,
	[KeyWorks]	nvarchar(50)		null,
	[Content]	ntext				Not null,
	constraint [PK_TRecord] Primary key nonclustered ([ID] asc),
	constraint [FK_TRecord_Type] Foreign key ([TypeID]) references [dbo].[TRecordType] ([ID])
) on [Primary]
Go

Create table [dbo].[TComment](
	[ID]		int Identity(1, 1)	Not null,
	[Type]		int					Not null,
	[FKID]		int					Not null,
	[Content]	nvarchar(500)		Not null,
	[Reply]		nvarchar(500)		null,
	[AddedDate]	datetime			Not null,
	[UserId]	int					null,		--评论人
	[Email]		nvarchar(100)		not null
) on [Primary]
Go

Create table [dbo].[TToolsType](
	[ID]		int Identity(1,1)	Not null,
	[Name]		nvarchar(20)			Not null,
	[Remark]	nvarchar(100)			null
	constraint [PK_TToolsType] Primary key nonclustered ([ID] asc),
) on [Primary]
go

Create table [dbo].[TTools](
	[ID]		int Identity(1, 1)	Not null,
	[Name]		nvarchar(200)		Not null,
	[TypeID]		int					Not null,
	[Pic]		nvarchar(200)		Not null,
	[Content]	ntext				Not null,
	[Version]	ntext				Not null,
	constraint [PK_TTools] Primary key nonclustered ([ID] asc),
	constraint [FK_TTools_Type] Foreign key ([TypeID]) references [dbo].[TToolsType] ([ID])
) on [Primary]
Go

Create table [dbo].[TToolsSoft](
	[ID]		int Identity(1, 1)	Not null,
	[ToolId]	int					not null,
	[Name]		nvarchar(200)		Not null,
	[Address]	nvarchar(400)		not null,
	[Remark]	nvarchar(200)		not null
	constraint [PK_TToolsSoft] Primary key nonclustered ([ID] asc),
	constraint [FK_TToolsSoft_ToolId] Foreign key ([ToolId]) references [dbo].[TTools] (ID)
) on [Primary]
go


--==========================================================================================
--Initialize data
--==========================================================================================
Insert into [TAdmin] ([UserCode], [Password], [UserName], [Level], [LastLogin], [Remark])
	values ('admin', '123', '管理员', 0, GETDATE(), '')
Go

Insert into [TRecordType] ([Name], [Remark]) values ('.Net(C#,VB)', '.Net(C#,VB)');
Insert into [TRecordType] ([Name], [Remark]) values ('Javascript', 'Javascript');
Insert into [TRecordType] ([Name], [Remark]) values ('AS3/Flex/Flash', 'AS3/Flex/Flash');
Insert into [TRecordType] ([Name], [Remark]) values ('SQL/DataBase', 'SQL/DataBase');
Insert into [TRecordType] ([Name], [Remark]) values ('CSS/HTML', 'CSS/HTML');

Insert into TRecord (Title, TypeID, [Status], Author, AddedDate, Summary, KeyWorks, Content)
values ('测试1', 1, 0, '树皮', getdate(), '', '', '测试1');
Insert into TRecord (Title, TypeID, [Status], Author, AddedDate, Summary, KeyWorks, Content)
values ('测试2', 2, 2, '树皮', getdate(), '', '', '测试2');